IFEWs

Notebook for acquiring, preparing and posting the data

Author: Julia Brittes Tuthill Date Start: June 21, 2022 Last Change: April 10, 2023

Project: IFEWs Visualization - Animal, Crop and Nrate data, Ns calculation

Setup

Import necessary libraries

Get Data

USDA

This section of the notebook refers to the IFEW acquiring of data from USDA Quickstats

Project Part: Query Learning USDA QuickStats API Make this section auto updatable yearly.

See Quick Stats (NASS) API user guide: https://quickstats.nass.usda.gov/api

Workflow of this section is illustraded below: image.png

Crop Data

Animal Data

Loop through all parameters

Prepare Animal and Crop data

Here, we will prepare the format of the dataframes. We want to unite all this with the Fertilizer rate later. So, our intent is to have the all the wanted data in one dataframe which include populations, counties and years. image-2.png

We will interpolate the animal data in order to populate empty years, since we only have data for the Census years and we want data for 1997 till 2019.

After we will prepare the crop data, also dealing with Nan values.

We will join this two data sets.

Animal

(D) = "Suppressed to avoid disclosing data for individual operations." This means that the data has been intentionally withheld to protect the privacy of individual farmers or operations.

It is generally not recommended to include suppressed data in the analysis as it can introduce bias and affect the accuracy of the model.

One approach to dealing with suppressed data in linear regression is to remove them from the dataset. This can be done using the Pandas drop() method or by selecting only the rows with non-suppressed data using boolean indexing.

Another approach is to impute missing values in the dataset. However, imputation of suppressed data is not recommended as it can introduce biases in the data and affect the accuracy of the model.

Therefore, it is recommended to remove the rows with suppressed data from the dataset before conducting linear regression.

In order to validate the interpolation, we use yearly data for Iowa and compared it with the sum of our results.

Now we can save the final animal data in animal

Crop

Get data

Understand the column names and units

  1. CAP = Corn Area Planted (acres)

  2. CGH= Corn Grain Area Harvested (acres)

  3. CGY = Corn Grain Yield (bu/ac)

  4. SAP = Soybeans planted (acres)

  5. SH = Soybeans Area Harvested (acres)

  6. SY = Soybeans Yield (bu/ac)

Deal with Nan values

No data for crops in Taylor in 2015.

Merge Crop and Animal

N fertilizer from Raster to Counties/AgDistricts feature class

All the data comes from Agricultural nitrogen fertilizer uses in the continental U.S. during 1850-2015: a set of gridded time-series data. PANGAEA and the added rates given by Peiyu Cao (Iowa State) (ALL DATA FROM FOLDER IN LB N/Acre) in the format of 4km grids with rates in lb/acre, from 1850 to 2019 (we are interested only in 1997 to present). They are working on updating the data till present time. So more data can be added using the following script.

The workflow of the processes covered here is illustraded below.

image-3.png

Rasters to Feature Class

First, we clip the Nrate files for Iowa boundaries.

Clip Iowa

Interpolate area

This section will transform data from raster to points so we can interpolate the data and generate data for each county This will be done here for year 2019. A for loop will make this for all necessary years (1997 to 2021)

Clean shapefile

Loop for all files of Nrate

Aggregate Shapefiles in One shapefile (temporal series)

Read in each shapefile as a separate GeoDataFrame and store them in a list:

Merge USDA and Nrate

Calculate Nsurplus variables (and others)

The script below will use Vishal's Nitrogen Surplus model according to the workflow in the figure below: image.png In Vishal's study "the MDAO approach is applied to the IFEW system to model the synergies of the inherently coupled food, water, and energy disciplines with the goal of meeting Iowa’s agricultural demands. The purpose of the model is to provide critical information regarding the complex interdependencies to enable efficient policy and decision making in Iowa with respect to the environmental impact of the nitrogen export."

In particular, the IFEWs model involves five individual systems representing weather, water, agriculture, animal agriculture,and energy. The below modeling addresses the agricutlure and water (nitrogen surplus as a water quality indicador) of the IFEWs.

image-2.png

"The calculation of nitrogen surplus (Ns) is based on the construction of a rough agronomic annual nitrogen budget (Blesh and Drinkwater, 2013; Jones et al., 2019a) given as: $$ Ns = CN + MN + FN - GN $$

where CN is the input from the application of commercial nitrogen, MN is the nitrogen generated from manure, FN is the nitrogen fixed by soybean crop, and GN is the nitrogen present in harvested grain."

"The agriculture system receives four input parameters: the corn yield ($x_1$), soybean yield ($x_2$), rate of commercial, nitrogen for corn ($x_3$), and the rate of commercial nitrogen, for soybean ($x_4$). The output parameters of the agriculture, system are CN, FN, and GN." In this current document, we make no distinction between rates of commercial nitrogen ($x_3$, $x_4$). The commercial nitrogen (CN) is defined as equal to the average of the spatially explicit time-series data set of nitrogen (N) fertilizer use in agricultural land of the continental U.S provided by Peiyu Cao in kg/ha.

$$ CN = N_{rate} kg/ha $$

"The biological nitrogen fixation from the soybean crop is estimated based on relationship provided by Barry et al. (1993) and is given by:" $$ FN = (81.1x_2-98.5)A_{soy}/A $$

where $A_{soy}$ represents Iowa soybean acreage while the $A$ represents the total area under corn and soybean crop (planted area), and $x_2$ in this case is the soybean yield in *tons per hectare.

"Lastly, nitrogen exported in the harvested corn and soybean grain is computed to be 6.4% nitrogen in the soybean seed and 1.18% nitrogen in the corn seed (Blesh and Drinkwater, 2013) and is defined as:"

$$ GN = \left(x_1\left(\frac{1.18}{100}\right)A_{corn} + x_2\left(\frac{6.4}{100}\right)A_{soy}\right)/A $$

Where the areas refer to harvested areas.

For this work, the yields comes from yearly USDA reports.

"The animal agriculture system receives four input parameters, namely, the total hog/pig population ($x_5$), number of beef cows ($x_6$), number of milk cows ($x_7$), and the number of other cattle ($x_8$) which includes the population steers, heifers, and slaughter cattle. According to USDA (2020), Iowa holds first rank in red meat, pork, and egg production, however, the amount of nitrogen in poultry is much less than hogs and cattle. Thus, to keep the minimum number of parameters in our system model, only hog/pig and cattle populations are used for the MN calculation.The annual manure nitrogen contribution of each animal type is given by (Gronberg and Arnold, 2017):

$$ MN_{animal} = PA_{MN}LF $$

where P, AMN and LF are livestock group population, nitrogen in animal manure and life cycle of animal. Table 1 provides the numerical values of the parameters used for calculation of MN for each livestock group. The total MN contribution is then normalized using area $A$ (crops planted area) is given as:

$$ MN = (MN_{Hogs} + MN_{BeefCattle} + MN_{MilkCow} + MN_{OtherCattle})/A $$

In the same way, the animal population uses USDA as the source, but because of the lack of yearly data (only Census years are available) we interpolated the USDA data extending our data availability.

image-3.png

More information can be found here System Modeling and Sensitivity Analysis of the Iowa Food-Water-Energy Nexus

Understand the column names and units

  1. CN = Commercial Nitrogen in kg/ha

  2. MN = Manure Nitrogen in kg/ha

  3. GN = Grain Nitrogen in kg/ha

  4. FN = Fixation Nitrogen in kg/ha

Commercial Nitrogen in kg/ha

Manure Nitrogen in kg/ha

Total Crop area planted - Apply manure nitrogen to planted areas.

Grain Nitrogen in kg/ha

Fixation Nitrogen in kg/ha

Planted Area Crops

For soybeans bushels consider 1 metric ton/hectare = 14.87 (15) bushels/acre from https://www.extension.iastate.edu/agdm/wholefarm/pdf/c6-80.pdf

Nitrogen Surplus

In kg/ha

Export data

Interactive Plot

As you can see, the coordinates are accurate up to 14 decimal places, which translates to cm accuracy. We don’t need that much accuracy. We will round off the coordinates to two decimal places (1.1 km accuracy). That’ll help reduce the size of the visualization.